package me.zhengjie.business.service.impl;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import me.zhengjie.business.domain.vo.GeneralQueryZhgVo;
import me.zhengjie.business.domain.vo.HljgDspVo;
import me.zhengjie.business.service.GeneralQueryService;
import me.zhengjie.business.service.dto.GeneralQueryCriteria;
import me.zhengjie.utils.FileUtil;
import me.zhengjie.utils.PageUtil;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Slf4j
@Service
@RequiredArgsConstructor
public class GeneralQueryServiceImpl implements GeneralQueryService {

    @PersistenceContext
    private EntityManager em;

    @Override
    public Object queryZhxx(GeneralQueryCriteria criteria, int[] startEnd) {

        String tempSql =  "select r1.bis_zhsq_id, " +
                        "       r1.bis_zhsq_hljg_id, " +
                        "       r1.bis_zhsq_snry_id, " +
                        "       r1.bis_zhsq_snry_xm, " +
                        "       r1.bis_zhsq_snry_xb, " +
                        "       r1.bis_zhsq_snry_sfz, " +
                        "       r1.bis_zhsq_snry_ybssqx bis_zhsq_snry_ybssqx_code, " +
                        "       (select t3.label from sys_dict_detail t3 where t3.dict_id=1001 and t3.value=r1.bis_zhsq_snry_ybssqx) bis_zhsq_snry_ybssqx_name, " +
                        "       to_char(r1.bis_zhsq_dykkrq, 'yyyy-mm-dd') bis_zhsq_dykkrq, " +
                        "       to_char(r1.bis_zhsq_dyjsrq, 'yyyy-mm-dd') bis_zhsq_dyjsrq, " +
                        "       r1.bis_zhsq_xsdyq, " +
                        "       r1.bis_zhsq_adl, " +
                        "       r1.bis_zhsq_zt, " +
                        "       r1.bis_zhsq_adl_pc, " +
                        "       r1.bis_zhsq_sndj, " +
                        "       r1.bis_zhsq_rybh, " +
                        "       r1.bis_zhsq_nl, " +
                        "       r1.bis_zhsq_lxdh, " +
                        "       r1.bis_zhsq_hldz_ssqx bis_zhsq_hldz_ssqx_code, " +
                        "       (select t3.label from sys_dict_detail t3 where t3.dict_id=251 and t3.value=r1.bis_zhsq_hldz_ssqx) bis_zhsq_hldz_ssqx_name, " +
                        "       r1.bis_zhsq_hldz_xxdz, " +
                        "       r1.bis_zhsq_hldz, " +
                        "       r1.bis_zhsq_jhrxm, " +
                        "       r1.bis_zhsq_jhrdh,  " +
                        "       r1.bis_zhsq_gx bis_zhsq_gx_code, " +
                        "       (select t3.label from sys_dict_detail t3 where t3.dict_id=325 and t3.value=r1.bis_zhsq_gx) bis_zhsq_gx_name, " +
                        "       r1.bis_zhsq_sqyy, " +
                        "       r1.bis_zhsq_ybkh, " +
                        "       r1.bis_zhsq_cbzt, " +
                        "       r1.bis_user_id, " +
                        "       r1.bis_sbgs_id, " +
                        "       r1.bis_pggs_id, " +
                        "       r1.bis_zhsq_grbh, " +
                        "       r1.bis_zhsq_zhlb bis_zhsq_zhlb_code, " +
                        "       (select t3.label from sys_dict_detail t3 where t3.dict_id=1284 and t3.value=r1.bis_zhsq_zhlb) bis_zhsq_zhlb_name, " +
                        "       r1.bis_zhsq_hljg_bm, " +
                        "       r2.bis_gsgl_qymc sbgsmc, " +
                        "       r3.bis_gsgl_qymc hljgmc, " +
                        "       r6.bis_fpry_rygl_id, " +
                        "       (select to_char(wm_concat(to_char(t1.bis_fpry_rygl_xm))) from bis_fpry t1 where r1.bis_zhsq_id = t1.bis_fpry_zhsq_id and t1.bis_fpry_rylx='RYLX01' ) qyry, " +
                        "       (select to_char(wm_concat(to_char(t1.bis_fpry_rygl_xm))) from bis_fpry t1 where r1.bis_zhsq_id = t1.bis_fpry_zhsq_id and t1.bis_fpry_rylx='RYLX02' ) pgry, " +
                        "       (select to_char(wm_concat(to_char(t1.bis_fpry_rygl_xm))) from bis_fpry t1 where r1.bis_zhsq_id = t1.bis_fpry_zhsq_id and t1.bis_fpry_zt='0' and t1.bis_fpry_rylx='RYLX03' ) hlry, " +
                        "       (select to_char(wm_concat(to_char(t1.bis_fpry_rygl_xm))) from bis_fpry t1 where r1.bis_zhsq_id = t1.bis_fpry_zhsq_id and t1.bis_fpry_rylx='RYLX04' ) fhry, " +
                        "       (select to_char(t2.lrsj, 'yyyy-mm-dd hh24:mi:ss') from bis_zhsq_history t2 inner join (select zhsq_id, max(lrsj) maxlrsj from bis_zhsq_history where type = '02' group by zhsq_id ) t3 on t2.zhsq_id=t3.zhsq_id and t2.lrsj=t3.maxlrsj where t2.zhsq_id = r1.bis_zhsq_id ) pgsj, " +
                        "       (select to_char(t2.lrsj, 'yyyy-mm-dd hh24:mi:ss') from bis_zhsq_history t2 inner join (select zhsq_id, max(lrsj) maxlrsj from bis_zhsq_history where type = '04' group by zhsq_id ) t3 on t2.zhsq_id=t3.zhsq_id and t2.lrsj=t3.maxlrsj where t2.zhsq_id=r1.bis_zhsq_id ) fhsj, " +
                        "       r1.lrsj, " +
                        "       r1.bis_zhsq_sqlb, " +
                        "       to_char(r1.bis_zhsq_tjsj, 'yyyy-mm-dd') bis_zhsq_tjsj, " +
                        "       r1.bis_zhsq_cblx, " +
                        "       r3.bis_gsgl_ssqx bis_gsgl_ssqx_code, " +
                        "       (select t3.label from sys_dict_detail t3 where t3.dict_id=251 and t3.value=r3.bis_gsgl_ssqx) bis_gsgl_ssqx_name, " +
                        "       (select t3.label from sys_dict_detail t3 where t3.dict_id=17260 and t3.value=r1.bis_zhsq_sqlb) bis_zhsq_sqlb_name, " +
                        "       r7.bis_gsgl_qymc pggsmc " +
                        "  from bis_zhsq r1 " +
                        "  left join bis_gsgl r2 on r1.bis_sbgs_id = r2.bis_gsgl_id " +
                        "  left join bis_gsgl r3 on r1.bis_zhsq_hljg_bm = r3.bis_gsgl_yljgdm " +
                        "  left join bis_qy r4 on r1.bis_zhsq_id = r4.bis_qy_zhsq_id and r4.bis_gsgl_iszf='1' " +
                        "  left join bis_gs r5 on r1.bis_zhsq_id = r5.bis_gs_zhid " +
                        "  left join bis_fpry r6 on r1.bis_zhsq_id = r6.bis_fpry_zhsq_id and r6.bis_fpry_zt='0' " +
                        "  left join bis_gsgl r7 on r1.bis_pggs_id = r7.bis_gsgl_id ";

        // 使用预编译防止sql注入
        String sql = "select * from ( " + tempSql + "  ) n where 1=1 ";

        String conditionSql1 = "";
        if(!StringUtils.isEmpty(criteria.getUserId())) {
            conditionSql1 += " and n.bis_fpry_rygl_id=:ryglId ";
        }
        if(!StringUtils.isEmpty(criteria.getDdbm())) {
            conditionSql1 += " and n.bis_zhsq_hljg_bm=:ddbm ";
        }
        if(!StringUtils.isEmpty(criteria.getZt())) {
            conditionSql1 += " and n.bis_zhsq_zt=:zt ";
        }
        if(!StringUtils.isEmpty(criteria.getTcq())) {
            conditionSql1 += " and n.bis_zhsq_snry_ybssqx_code=:tcq ";
        }
        if(!StringUtils.isEmpty(criteria.getZhlb())) {
            conditionSql1 += " and n.bis_zhsq_zhlb_code=:zhlb ";
        }
        if(!StringUtils.isEmpty(criteria.getXm())) {
            conditionSql1 += " and n.bis_zhsq_snry_xm like :xm ";
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            conditionSql1 += " and n.bis_zhsq_snry_sfz like :sfz ";
        }
        if(!StringUtils.isEmpty(criteria.getSbgsId())) {
            conditionSql1 += " and n.bis_sbgs_id = :sbgsId ";
        }
        if(!StringUtils.isEmpty(criteria.getInZt())) {
            conditionSql1 += " and n.bis_zhsq_zt in :ztList ";
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqCblx())) {
            conditionSql1 += " and n.bis_zhsq_cblx = :cblx ";
        }
        if(!StringUtils.isEmpty(criteria.getSndj())) {
            conditionSql1 += " and n.bis_zhsq_sndj = :sndj ";
        }
        if(!StringUtils.isEmpty(criteria.getBisGsglSsqxCode())) {
            conditionSql1 += " and n.bis_gsgl_ssqx_code = :gsglSsqx ";
        }

        sql += conditionSql1;

        Query query = em.createNativeQuery(sql);
        query.setFirstResult(startEnd[0]);
        query.setMaxResults(startEnd[1] - startEnd[0]);

        if(!StringUtils.isEmpty(criteria.getUserId())) {
            query.setParameter("ryglId", criteria.getUserId());
        }
        if(!StringUtils.isEmpty(criteria.getDdbm())) {
            query.setParameter("ddbm", criteria.getDdbm());
        }
        if(!StringUtils.isEmpty(criteria.getZt())) {
            query.setParameter("zt", criteria.getZt());
        }
        if(!StringUtils.isEmpty(criteria.getTcq())) {
            query.setParameter("tcq", criteria.getTcq());
        }
        if(!StringUtils.isEmpty(criteria.getZhlb())) {
            query.setParameter("zhlb", criteria.getZhlb());
        }
        if(!StringUtils.isEmpty(criteria.getXm())) {
            query.setParameter("xm", "%" + criteria.getXm() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            query.setParameter("sfz", "%" + criteria.getSfz() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSbgsId())) {
            query.setParameter("sbgsId", criteria.getSbgsId());
        }
        if(!StringUtils.isEmpty(criteria.getInZt())) {
            query.setParameter("ztList", criteria.getZtList());
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqCblx())) {
            query.setParameter("cblx", criteria.getBisZhsqCblx());
        }
        if(!StringUtils.isEmpty(criteria.getSndj())) {
            query.setParameter("sndj", criteria.getSndj());
        }
        if(!StringUtils.isEmpty(criteria.getBisGsglSsqxCode())) {
            query.setParameter("gsglSsqx", criteria.getBisGsglSsqxCode());
        }
        List result = query.getResultList();
        List<GeneralQueryZhgVo> hljgDspVos = new ArrayList<>();
        for (Object obj : result) {
            Object[] arr = (Object[]) obj;
            GeneralQueryZhgVo vo = new GeneralQueryZhgVo();
            vo.setBisZhsqId(Long.parseLong(arr[0].toString()));
            vo.setBisZhsqSnryXm(arr[3]);
            vo.setBisZhsqSnryXb(arr[4]);
            vo.setBisZhsqSnrySfz(arr[5]);
            vo.setBisZhsqSnryYbssqxCode(arr[6]);
            vo.setBisZhsqSnryYbssqxName(arr[7]);
            vo.setBisZhsqDykkrq(arr[8]);
            vo.setBisZhsqDyjsrq(arr[9]);
            vo.setBisZhsqXsdyq(arr[10]);
            vo.setBisZhsqAdl(arr[11]);
            vo.setBisZhsqZt(arr[12]);
            vo.setBisZhsqAdlPc(arr[13]);
            vo.setBisZhsqSndj(arr[14]);
            vo.setBisZhsqRybh(arr[15]);
            vo.setBisZhsqNl(arr[16]);
            vo.setBisZhsqLxdh(arr[17]);
            vo.setBisZhsqHldzSsqxCode(arr[18]);
            vo.setBisZhsqHldzSsqxName(arr[19]);
            vo.setBisZhsqHldzXxdz(arr[20]);
            vo.setBisZhsqHldz(arr[21]);
            vo.setBisZhsqJhrxm(arr[22]);
            vo.setBisZhsqJhrdh(arr[23]);
            vo.setBisZhsqGxCode(arr[24]);
            vo.setBisZhsqGxName(arr[25]);
            vo.setBisZhsqSqyy(arr[26]);
            vo.setBisZhsqYbkh(arr[27]);
            vo.setBisZhsqCbzt(arr[28]);
            vo.setBisUserId(arr[29]);
            vo.setBisSbgsId(arr[30]);
            vo.setBisPggsId(arr[31]);
            vo.setBisZhsqGrbh(arr[32]);
            vo.setBisZhsqZhlbCode(arr[33]);
            vo.setBisZhsqZhlbName(arr[34]);
            vo.setBisZhsqHljgBm(arr[35]);
            vo.setBisSbgsQymc(arr[36]);
            vo.setBisHljgQymc(arr[37]);
            vo.setQyry(arr[39]);
            vo.setPgry(arr[40]);
            vo.setHlry(arr[41]);
            vo.setFhry(arr[42]);
            vo.setPgsj(arr[43]);
            vo.setFhsj(arr[44]);
            vo.setLrsj(arr[45]);
            vo.setBisZhsqSqlb(arr[46]);
            vo.setBisZhsqTjsj(arr[47]);
            vo.setBisZhsqCblx(arr[48]);
            vo.setBisZhsqSqlbName(arr[51]);
            vo.setBisPggsQymc(arr[52]);
            hljgDspVos.add(vo);
        }
        String countSql = "select count(1) from ( " + tempSql + "  ) n where 1=1 ";
        String conditionSql2 = "";
        if(!StringUtils.isEmpty(criteria.getUserId())) {
            conditionSql2 += " and n.bis_fpry_rygl_id=:ryglId ";
        }
        if(!StringUtils.isEmpty(criteria.getDdbm())) {
            conditionSql2 += " and n.bis_zhsq_hljg_bm=:ddbm ";
        }
        if(!StringUtils.isEmpty(criteria.getZt())) {
            conditionSql2 += " and n.bis_zhsq_zt=:zt ";
        }
        if(!StringUtils.isEmpty(criteria.getTcq())) {
            conditionSql2 += " and n.bis_zhsq_snry_ybssqx_code=:tcq ";
        }
        if(!StringUtils.isEmpty(criteria.getZhlb())) {
            conditionSql2 += " and n.bis_zhsq_zhlb_code=:zhlb ";
        }
        if(!StringUtils.isEmpty(criteria.getXm())) {
            conditionSql2 += " and n.bis_zhsq_snry_xm like :xm ";
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            conditionSql2 += " and n.bis_zhsq_snry_sfz like :sfz ";
        }
        if(!StringUtils.isEmpty(criteria.getSbgsId())) {
            conditionSql2 += " and n.bis_sbgs_id = :sbgsId ";
        }
        if(!StringUtils.isEmpty(criteria.getInZt())) {
            conditionSql2 += " and n.bis_zhsq_zt in :ztList ";
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqCblx())) {
            conditionSql2 += " and n.bis_zhsq_cblx = :cblx ";
        }
        if(!StringUtils.isEmpty(criteria.getSndj())) {
            conditionSql2 += " and n.bis_zhsq_sndj = :sndj ";
        }
        if(!StringUtils.isEmpty(criteria.getBisGsglSsqxCode())) {
            conditionSql2 += " and n.bis_gsgl_ssqx_code = :gsglSsqx ";
        }
        countSql += conditionSql2;
        Query queryCount = em.createNativeQuery(countSql);

        if(!StringUtils.isEmpty(criteria.getUserId())) {
            queryCount.setParameter("ryglId", criteria.getUserId());
        }
        if(!StringUtils.isEmpty(criteria.getDdbm())) {
            queryCount.setParameter("ddbm", criteria.getDdbm());
        }
        if(!StringUtils.isEmpty(criteria.getZt())) {
            queryCount.setParameter("zt", criteria.getZt());
        }
        if(!StringUtils.isEmpty(criteria.getTcq())) {
            queryCount.setParameter("tcq", criteria.getTcq());
        }
        if(!StringUtils.isEmpty(criteria.getZhlb())) {
            queryCount.setParameter("zhlb", criteria.getZhlb());
        }
        if(!StringUtils.isEmpty(criteria.getXm())) {
            queryCount.setParameter("xm", "%" + criteria.getXm() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            queryCount.setParameter("sfz", "%" + criteria.getSfz() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSbgsId())) {
            queryCount.setParameter("sbgsId", criteria.getSbgsId());
        }
        if(!StringUtils.isEmpty(criteria.getInZt())) {
            queryCount.setParameter("ztList", criteria.getZtList());
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqCblx())) {
            queryCount.setParameter("cblx", criteria.getBisZhsqCblx());
        }
        if(!StringUtils.isEmpty(criteria.getSndj())) {
            queryCount.setParameter("sndj", criteria.getSndj());
        }
        if(!StringUtils.isEmpty(criteria.getBisGsglSsqxCode())) {
            queryCount.setParameter("gsglSsqx", criteria.getBisGsglSsqxCode());
        }
        Object totalElements = queryCount.getSingleResult();
        log.info("totalElements {} ", totalElements);
        Map<String,Object> m = PageUtil.toPage(hljgDspVos, totalElements);
        return m;
    }

    @Override
    public Object queryCommonZhxx(GeneralQueryCriteria criteria, int[] startEnd) {

        String tempSql =  "select r1.bis_zhsq_id, " +
                "       r1.bis_zhsq_hljg_id, " +
                "       r1.bis_zhsq_snry_id, " +
                "       r1.bis_zhsq_snry_xm, " +
                "       r1.bis_zhsq_snry_xb, " +
                "       r1.bis_zhsq_snry_sfz, " +
                "       r1.bis_zhsq_snry_ybssqx bis_zhsq_snry_ybssqx_code, " +
                "       (select t3.label from sys_dict_detail t3 where t3.dict_id=1001 and t3.value=r1.bis_zhsq_snry_ybssqx) bis_zhsq_snry_ybssqx_name, " +
                "       to_char(r1.bis_zhsq_dykkrq, 'yyyy-mm-dd') bis_zhsq_dykkrq, " +
                "       to_char(r1.bis_zhsq_dyjsrq, 'yyyy-mm-dd') bis_zhsq_dyjsrq, " +
                "       r1.bis_zhsq_xsdyq, " +
                "       r1.bis_zhsq_adl, " +
                "       r1.bis_zhsq_zt, " +
                "       r1.bis_zhsq_adl_pc, " +
                "       r1.bis_zhsq_sndj, " +
                "       r1.bis_zhsq_rybh, " +
                "       r1.bis_zhsq_nl, " +
                "       r1.bis_zhsq_lxdh, " +
                "       r1.bis_zhsq_hldz_ssqx bis_zhsq_hldz_ssqx_code, " +
                "       (select t3.label from sys_dict_detail t3 where t3.dict_id=251 and t3.value=r1.bis_zhsq_hldz_ssqx) bis_zhsq_hldz_ssqx_name, " +
                "       r1.bis_zhsq_hldz_xxdz, " +
                "       r1.bis_zhsq_hldz, " +
                "       r1.bis_zhsq_jhrxm, " +
                "       r1.bis_zhsq_jhrdh,  " +
                "       r1.bis_zhsq_gx bis_zhsq_gx_code, " +
                "       (select t3.label from sys_dict_detail t3 where t3.dict_id=325 and t3.value=r1.bis_zhsq_gx) bis_zhsq_gx_name, " +
                "       r1.bis_zhsq_sqyy, " +
                "       r1.bis_zhsq_ybkh, " +
                "       r1.bis_zhsq_cbzt, " +
                "       r1.bis_user_id, " +
                "       r1.bis_sbgs_id, " +
                "       r1.bis_pggs_id, " +
                "       r1.bis_zhsq_grbh, " +
                "       r1.bis_zhsq_zhlb bis_zhsq_zhlb_code, " +
                "       (select t3.label from sys_dict_detail t3 where t3.dict_id=1284 and t3.value=r1.bis_zhsq_zhlb) bis_zhsq_zhlb_name, " +
                "       r1.bis_zhsq_hljg_bm, " +
                "       r2.bis_gsgl_qymc sbgsmc, " +
                "       r3.bis_gsgl_qymc hljgmc, " +
                "       (select to_char(wm_concat(to_char(t1.bis_fpry_rygl_xm))) from bis_fpry t1 where r1.bis_zhsq_id = t1.bis_fpry_zhsq_id and t1.bis_fpry_rylx='RYLX01' ) qyry, " +
                "       case when r1.bis_zhsq_zt = '54' then (select to_char(t1.bis_fpry_rygl_xm) from bis_fpry  t1 where r1.bis_zhsq_id = t1.bis_fpry_zhsq_id and t1.bis_fpry_zt = '0' and  r1.bis_zhsq_zt='54' ) else (select to_char(wm_concat(to_char(t1.LRRXM))) from bis_zhsq_history  t1 where r1.bis_zhsq_id = t1.zhsq_id and t1.type = '02'  ) end pgry," +
                "       (select to_char(wm_concat(to_char(t1.bis_fpry_rygl_xm))) from bis_fpry t1 where r1.bis_zhsq_id = t1.bis_fpry_zhsq_id and t1.bis_fpry_zt='0' and t1.bis_fpry_rylx='RYLX03' ) hlry, " +
                "       case when r1.bis_zhsq_zt = '55' then (select to_char(t1.bis_fpry_rygl_xm) from bis_fpry  t1 where r1.bis_zhsq_id = t1.bis_fpry_zhsq_id and t1.bis_fpry_zt = '0' and  r1.bis_zhsq_zt='55' ) else (select to_char(wm_concat(to_char(t1.LRRXM))) from bis_zhsq_history  t1 where r1.bis_zhsq_id = t1.zhsq_id and t1.type = '04'  ) end fhry," +
                "       (select to_char(t2.lrsj, 'yyyy-mm-dd hh24:mi:ss') from bis_zhsq_history t2 inner join (select zhsq_id, max(lrsj) maxlrsj from bis_zhsq_history where type = '02' group by zhsq_id ) t3 on t2.zhsq_id=t3.zhsq_id and t2.lrsj=t3.maxlrsj where t2.zhsq_id = r1.bis_zhsq_id ) pgsj, " +
                "       (select to_char(t2.lrsj, 'yyyy-mm-dd hh24:mi:ss') from bis_zhsq_history t2 inner join (select zhsq_id, max(lrsj) maxlrsj from bis_zhsq_history where type = '04' group by zhsq_id ) t3 on t2.zhsq_id=t3.zhsq_id and t2.lrsj=t3.maxlrsj where t2.zhsq_id=r1.bis_zhsq_id ) fhsj, " +
                "       r1.lrsj, " +
                "       r1.bis_zhsq_sqlb, " +
                "       r1.bis_zhsq_tjsj, " +
                "       r1.bis_zhsq_cblx, " +
                "       r1.bis_old_id, " +
                "       r1.bis_zhsq_hj_sndj, " +
                "       r6.bis_gsgl_qymc pggsmc " +
                "  from bis_zhsq r1 " +
                "  left join bis_gsgl r2 on r1.bis_sbgs_id = r2.bis_gsgl_id " +
                "  left join bis_gsgl r3 on r1.bis_zhsq_hljg_bm = r3.bis_gsgl_yljgdm " +
                "  left join bis_qy r4 on r1.bis_zhsq_id = r4.bis_qy_zhsq_id and r4.bis_gsgl_iszf='1' " +
                "  left join bis_gs r5 on r1.bis_zhsq_id = r5.bis_gs_zhid " +
                "  left join bis_gsgl r6 on r1.bis_pggs_id = r6.bis_gsgl_id ";

        // 使用预编译防止sql注入
        String sql = "select * from ( " + tempSql + "  ) n where 1=1 ";

        String conditionSql1 = "";
        if(!StringUtils.isEmpty(criteria.getDdbm())) {
            conditionSql1 += " and n.bis_zhsq_hljg_bm=:ddbm ";
        }
        if(!StringUtils.isEmpty(criteria.getZt())) {
            conditionSql1 += " and n.bis_zhsq_zt=:zt ";
        }
        if(!StringUtils.isEmpty(criteria.getTcq())) {
            conditionSql1 += " and n.bis_zhsq_snry_ybssqx_code=:tcq ";
        }
        if(!StringUtils.isEmpty(criteria.getZhlb())) {
            conditionSql1 += " and n.bis_zhsq_zhlb_code=:zhlb ";
        }
        if(!StringUtils.isEmpty(criteria.getNotInCenter())) {
            conditionSql1 += " and n.bis_zhsq_snry_ybssqx_code != '220101' ";
        }
        if(!StringUtils.isEmpty(criteria.getInSbgs())) {
            conditionSql1 += " and n.bis_sbgs_id is not null ";
        }
        if(!StringUtils.isEmpty(criteria.getXm())) {
            conditionSql1 += " and n.bis_zhsq_snry_xm like :xm ";
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            conditionSql1 += " and n.bis_zhsq_snry_sfz like :sfz ";
        }
        if(!StringUtils.isEmpty(criteria.getSbgsId())) {
            conditionSql1 += " and n.bis_sbgs_id = :sbgsId ";
        }
        if(!StringUtils.isEmpty(criteria.getInZt())) {
            conditionSql1 += " and n.bis_zhsq_zt in :ztList ";
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqCblx())) {
            conditionSql1 += " and n.bis_zhsq_cblx = :cblx ";
        }
        if(!StringUtils.isEmpty(criteria.getSndj())) {
            conditionSql1 += " and n.bis_zhsq_sndj = :sndj ";
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqDyjsrq2())) {
            conditionSql1 += " and n.bis_zhsq_dyjsrq <= :jsrq2 ";
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqDyjsrq1())) {
            conditionSql1 += " and n.bis_zhsq_dyjsrq >= :jsrq1 ";
        }

        sql += conditionSql1 + " order by n.lrsj desc ";

        Query query = em.createNativeQuery(sql);
        query.setFirstResult(startEnd[0]);
        query.setMaxResults(startEnd[1] - startEnd[0]);

        if(!StringUtils.isEmpty(criteria.getUserId())) {
            query.setParameter("ryglId", criteria.getUserId());
        }
        if(!StringUtils.isEmpty(criteria.getDdbm())) {
            query.setParameter("ddbm", criteria.getDdbm());
        }
        if(!StringUtils.isEmpty(criteria.getZt())) {
            query.setParameter("zt", criteria.getZt());
        }
        if(!StringUtils.isEmpty(criteria.getTcq())) {
            query.setParameter("tcq", criteria.getTcq());
        }
        if(!StringUtils.isEmpty(criteria.getZhlb())) {
            query.setParameter("zhlb", criteria.getZhlb());
        }
        if(!StringUtils.isEmpty(criteria.getXm())) {
            query.setParameter("xm", "%" + criteria.getXm() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            query.setParameter("sfz", "%" + criteria.getSfz() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSbgsId())) {
            query.setParameter("sbgsId", criteria.getSbgsId());
        }
        if(!StringUtils.isEmpty(criteria.getInZt())) {
            query.setParameter("ztList", criteria.getZtList());
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqCblx())) {
            query.setParameter("cblx", criteria.getBisZhsqCblx());
        }
        if(!StringUtils.isEmpty(criteria.getSndj())) {
            query.setParameter("sndj", criteria.getSndj());
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqDyjsrq2())) {
            query.setParameter("jsrq2", criteria.getBisZhsqDyjsrq2());
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqDyjsrq1())) {
            query.setParameter("jsrq1", criteria.getBisZhsqDyjsrq1());
        }
        List result = query.getResultList();
        List<GeneralQueryZhgVo> hljgDspVos = new ArrayList<>();
        for (Object obj : result) {
            Object[] arr = (Object[]) obj;
            GeneralQueryZhgVo vo = new GeneralQueryZhgVo();
            vo.setBisZhsqId(Long.parseLong(arr[0].toString()));
            vo.setBisZhsqSnryXm(arr[3]);
            vo.setBisZhsqSnryXb(arr[4]);
            vo.setBisZhsqSnrySfz(arr[5]);
            vo.setBisZhsqSnryYbssqxCode(arr[6]);
            vo.setBisZhsqSnryYbssqxName(arr[7]);
            vo.setBisZhsqDykkrq(arr[8]);
            vo.setBisZhsqDyjsrq(arr[9]);
            vo.setBisZhsqXsdyq(arr[10]);
            vo.setBisZhsqAdl(arr[11]);
            vo.setBisZhsqZt(arr[12]);
            vo.setBisZhsqAdlPc(arr[13]);
            vo.setBisZhsqSndj(arr[14]);
            vo.setBisZhsqRybh(arr[15]);
            vo.setBisZhsqNl(arr[16]);
            vo.setBisZhsqLxdh(arr[17]);
            vo.setBisZhsqHldzSsqxCode(arr[18]);
            vo.setBisZhsqHldzSsqxName(arr[19]);
            vo.setBisZhsqHldzXxdz(arr[20]);
            vo.setBisZhsqHldz(arr[21]);
            vo.setBisZhsqJhrxm(arr[22]);
            vo.setBisZhsqJhrdh(arr[23]);
            vo.setBisZhsqGxCode(arr[24]);
            vo.setBisZhsqGxName(arr[25]);
            vo.setBisZhsqSqyy(arr[26]);
            vo.setBisZhsqYbkh(arr[27]);
            vo.setBisZhsqCbzt(arr[28]);
            vo.setBisUserId(arr[29]);
            vo.setBisSbgsId(arr[30]);
            vo.setBisPggsId(arr[31]);
            vo.setBisZhsqGrbh(arr[32]);
            vo.setBisZhsqZhlbCode(arr[33]);
            vo.setBisZhsqZhlbName(arr[34]);
            vo.setBisZhsqHljgBm(arr[35]);
            vo.setBisSbgsQymc(arr[36]);
            vo.setBisHljgQymc(arr[37]);
            vo.setQyry(arr[38]);
            vo.setPgry(arr[39]);
            vo.setHlry(arr[40]);
            vo.setFhry(arr[41]);
            vo.setPgsj(arr[42]);
            vo.setFhsj(arr[43]);
            vo.setLrsj(arr[44]);
            vo.setBisZhsqSqlb(arr[45]);
            vo.setBisZhsqTjsj(arr[46]);
            vo.setBisZhsqCblx(arr[47]);
            vo.setBisOldId(arr[48] == null ? null : Long.parseLong(arr[48].toString()));
            vo.setBisZhsqHjSndj(arr[49]);
            vo.setBisPggsQymc(arr[50]);
            hljgDspVos.add(vo);
        }
        String countSql = "select count(1) from ( " + tempSql + "  ) n where 1=1 ";
        String conditionSql2 = "";
        if(!StringUtils.isEmpty(criteria.getDdbm())) {
            conditionSql2 += " and n.bis_zhsq_hljg_bm=:ddbm ";
        }
        if(!StringUtils.isEmpty(criteria.getZt())) {
            conditionSql2 += " and n.bis_zhsq_zt=:zt ";
        }
        if(!StringUtils.isEmpty(criteria.getTcq())) {
            conditionSql2 += " and n.bis_zhsq_snry_ybssqx_code=:tcq ";
        }
        if(!StringUtils.isEmpty(criteria.getZhlb())) {
            conditionSql2 += " and n.bis_zhsq_zhlb_code=:zhlb ";
        }
        if(!StringUtils.isEmpty(criteria.getNotInCenter())) {
            conditionSql2 += " and n.bis_zhsq_snry_ybssqx_code != '220101' ";
        }
        if(!StringUtils.isEmpty(criteria.getInSbgs())) {
            conditionSql2 += " and n.bis_sbgs_id is not null ";
        }
        if(!StringUtils.isEmpty(criteria.getXm())) {
            conditionSql2 += " and n.bis_zhsq_snry_xm like :xm ";
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            conditionSql2 += " and n.bis_zhsq_snry_sfz like :sfz ";
        }
        if(!StringUtils.isEmpty(criteria.getSbgsId())) {
            conditionSql2 += " and n.bis_sbgs_id = :sbgsId ";
        }
        if(!StringUtils.isEmpty(criteria.getInZt())) {
            conditionSql2 += " and n.bis_zhsq_zt in :ztList ";
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqCblx())) {
            conditionSql2 += " and n.bis_zhsq_cblx = :cblx ";
        }
        if(!StringUtils.isEmpty(criteria.getSndj())) {
            conditionSql2 += " and n.bis_zhsq_sndj = :sndj ";
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqDyjsrq2())) {
            conditionSql2 += " and n.bis_zhsq_dyjsrq <= :jsrq2 ";
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqDyjsrq1())) {
            conditionSql2 += " and n.bis_zhsq_dyjsrq >= :jsrq1 ";
        }
        countSql += conditionSql2;
        Query queryCount = em.createNativeQuery(countSql);

        if(!StringUtils.isEmpty(criteria.getUserId())) {
            queryCount.setParameter("ryglId", criteria.getUserId());
        }
        if(!StringUtils.isEmpty(criteria.getDdbm())) {
            queryCount.setParameter("ddbm", criteria.getDdbm());
        }
        if(!StringUtils.isEmpty(criteria.getZt())) {
            queryCount.setParameter("zt", criteria.getZt());
        }
        if(!StringUtils.isEmpty(criteria.getTcq())) {
            queryCount.setParameter("tcq", criteria.getTcq());
        }
        if(!StringUtils.isEmpty(criteria.getZhlb())) {
            queryCount.setParameter("zhlb", criteria.getZhlb());
        }
        if(!StringUtils.isEmpty(criteria.getXm())) {
            queryCount.setParameter("xm", "%" + criteria.getXm() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            queryCount.setParameter("sfz", "%" + criteria.getSfz() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSbgsId())) {
            queryCount.setParameter("sbgsId", criteria.getSbgsId());
        }
        if(!StringUtils.isEmpty(criteria.getInZt())) {
            queryCount.setParameter("ztList", criteria.getZtList());
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqCblx())) {
            queryCount.setParameter("cblx", criteria.getBisZhsqCblx());
        }
        if(!StringUtils.isEmpty(criteria.getSndj())) {
            queryCount.setParameter("sndj", criteria.getSndj());
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqDyjsrq2())) {
            queryCount.setParameter("jsrq2", criteria.getBisZhsqDyjsrq2());
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqDyjsrq1())) {
            queryCount.setParameter("jsrq1", criteria.getBisZhsqDyjsrq1());
        }
        Object totalElements = queryCount.getSingleResult();
        Map<String,Object> m = PageUtil.toPage(hljgDspVos, totalElements);
        return m;
    }

    @Override
    public void queryCommonZhxxDown(GeneralQueryCriteria criteria, HttpServletResponse response) throws IOException {

        String tempSql =  "select r1.bis_zhsq_id, " +
                "       r1.bis_zhsq_hljg_id, " +
                "       r1.bis_zhsq_snry_id, " +
                "       r1.bis_zhsq_snry_xm, " +
                "       r1.bis_zhsq_snry_xb, " +
                "       r1.bis_zhsq_snry_sfz, " +
                "       r1.bis_zhsq_snry_ybssqx bis_zhsq_snry_ybssqx_code, " +
                "       (select t3.label from sys_dict_detail t3 where t3.dict_id=1001 and t3.value=r1.bis_zhsq_snry_ybssqx) bis_zhsq_snry_ybssqx_name, " +
                "       to_char(r1.bis_zhsq_dykkrq, 'yyyy-mm-dd') bis_zhsq_dykkrq, " +
                "       to_char(r1.bis_zhsq_dyjsrq, 'yyyy-mm-dd') bis_zhsq_dyjsrq, " +
                "       r1.bis_zhsq_xsdyq, " +
                "       r1.bis_zhsq_adl, " +
                "       r1.bis_zhsq_zt, " +
                "       r1.bis_zhsq_adl_pc, " +
                "       r1.bis_zhsq_sndj, " +
                "       r1.bis_zhsq_rybh, " +
                "       r1.bis_zhsq_nl, " +
                "       r1.bis_zhsq_lxdh, " +
                "       r1.bis_zhsq_hldz_ssqx bis_zhsq_hldz_ssqx_code, " +
                "       (select t3.label from sys_dict_detail t3 where t3.dict_id=251 and t3.value=r1.bis_zhsq_hldz_ssqx) bis_zhsq_hldz_ssqx_name, " +
                "       r1.bis_zhsq_hldz_xxdz, " +
                "       r1.bis_zhsq_hldz, " +
                "       r1.bis_zhsq_jhrxm, " +
                "       r1.bis_zhsq_jhrdh,  " +
                "       r1.bis_zhsq_gx bis_zhsq_gx_code, " +
                "       (select t3.label from sys_dict_detail t3 where t3.dict_id=325 and t3.value=r1.bis_zhsq_gx) bis_zhsq_gx_name, " +
                "       r1.bis_zhsq_sqyy, " +
                "       r1.bis_zhsq_ybkh, " +
                "       r1.bis_zhsq_cbzt, " +
                "       r1.bis_user_id, " +
                "       r1.bis_sbgs_id, " +
                "       r1.bis_pggs_id, " +
                "       r1.bis_zhsq_grbh, " +
                "       r1.bis_zhsq_zhlb bis_zhsq_zhlb_code, " +
                "       (select t3.label from sys_dict_detail t3 where t3.dict_id=1284 and t3.value=r1.bis_zhsq_zhlb) bis_zhsq_zhlb_name, " +
                "       r1.bis_zhsq_hljg_bm, " +
                "       r2.bis_gsgl_qymc sbgsmc, " +
                "       r3.bis_gsgl_qymc hljgmc, " +
                "       (select to_char(wm_concat(to_char(t1.bis_fpry_rygl_xm))) from bis_fpry t1 where r1.bis_zhsq_id = t1.bis_fpry_zhsq_id and t1.bis_fpry_rylx='RYLX01' ) qyry, " +
                "       case when r1.bis_zhsq_zt = '54' then (select to_char(t1.bis_fpry_rygl_xm) from bis_fpry  t1 where r1.bis_zhsq_id = t1.bis_fpry_zhsq_id and t1.bis_fpry_zt = '0' and  r1.bis_zhsq_zt='54' ) else (select to_char(wm_concat(to_char(t1.LRRXM))) from bis_zhsq_history  t1 where r1.bis_zhsq_id = t1.zhsq_id and t1.type = '02'  ) end pgry," +
                "       (select to_char(wm_concat(to_char(t1.bis_fpry_rygl_xm))) from bis_fpry t1 where r1.bis_zhsq_id = t1.bis_fpry_zhsq_id and t1.bis_fpry_zt='0' and t1.bis_fpry_rylx='RYLX03' ) hlry, " +
                "       case when r1.bis_zhsq_zt = '55' then (select to_char(t1.bis_fpry_rygl_xm) from bis_fpry  t1 where r1.bis_zhsq_id = t1.bis_fpry_zhsq_id and t1.bis_fpry_zt = '0' and  r1.bis_zhsq_zt='55' ) else (select to_char(wm_concat(to_char(t1.LRRXM))) from bis_zhsq_history  t1 where r1.bis_zhsq_id = t1.zhsq_id and t1.type = '04'  ) end fhry," +
                "       (select to_char(t2.lrsj, 'yyyy-mm-dd hh24:mi:ss') from bis_zhsq_history t2 inner join (select zhsq_id, max(lrsj) maxlrsj from bis_zhsq_history where type = '02' group by zhsq_id ) t3 on t2.zhsq_id=t3.zhsq_id and t2.lrsj=t3.maxlrsj where t2.zhsq_id = r1.bis_zhsq_id ) pgsj, " +
                "       (select to_char(t2.lrsj, 'yyyy-mm-dd hh24:mi:ss') from bis_zhsq_history t2 inner join (select zhsq_id, max(lrsj) maxlrsj from bis_zhsq_history where type = '04' group by zhsq_id ) t3 on t2.zhsq_id=t3.zhsq_id and t2.lrsj=t3.maxlrsj where t2.zhsq_id=r1.bis_zhsq_id ) fhsj, " +
                "       to_char(r1.lrsj, 'yyyy-mm-dd hh24:mi:ss') , " +
                "       r1.bis_zhsq_sqlb, " +
                "       r1.bis_zhsq_tjsj, " +
                "       r1.bis_zhsq_cblx, " +
                "       (select t3.label from sys_dict_detail t3 where t3.dict_id=451 and t3.value=r1.bis_zhsq_zt) bis_zhsq_zt_name, " +
                "       r1.lrsj " +
                "  from bis_zhsq r1 " +
                "  left join bis_gsgl r2 on r1.bis_sbgs_id = r2.bis_gsgl_id " +
                "  left join bis_gsgl r3 on r1.bis_zhsq_hljg_bm = r3.bis_gsgl_yljgdm " +
                "  left join bis_qy r4 on r1.bis_zhsq_id = r4.bis_qy_zhsq_id and r4.bis_gsgl_iszf='1' " +
                "  left join bis_gs r5 on r1.bis_zhsq_id = r5.bis_gs_zhid ";

        // 使用预编译防止sql注入
        String sql = "select * from ( " + tempSql + "  ) n where 1=1 ";

        String conditionSql1 = "";
        if(!StringUtils.isEmpty(criteria.getDdbm())) {
            conditionSql1 += " and n.bis_zhsq_hljg_bm=:ddbm ";
        }
        if(!StringUtils.isEmpty(criteria.getZt())) {
            conditionSql1 += " and n.bis_zhsq_zt=:zt ";
        }
        if(!StringUtils.isEmpty(criteria.getTcq())) {
            conditionSql1 += " and n.bis_zhsq_snry_ybssqx_code=:tcq ";
        }
        if(!StringUtils.isEmpty(criteria.getZhlb())) {
            conditionSql1 += " and n.bis_zhsq_zhlb_code=:zhlb ";
        }
        if(!StringUtils.isEmpty(criteria.getNotInCenter())) {
            conditionSql1 += " and n.bis_zhsq_snry_ybssqx_code != '220101' ";
        }
        if(!StringUtils.isEmpty(criteria.getInSbgs())) {
            conditionSql1 += " and n.bis_sbgs_id is not null ";
        }
        if(!StringUtils.isEmpty(criteria.getXm())) {
            conditionSql1 += " and n.bis_zhsq_snry_xm like :xm ";
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            conditionSql1 += " and n.bis_zhsq_snry_sfz like :sfz ";
        }
        if(!StringUtils.isEmpty(criteria.getSbgsId())) {
            conditionSql1 += " and n.bis_sbgs_id = :sbgsId ";
        }
        if(!StringUtils.isEmpty(criteria.getInZt())) {
            conditionSql1 += " and n.bis_zhsq_zt in :ztList ";
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqCblx())) {
            conditionSql1 += " and n.bis_zhsq_cblx = :cblx ";
        }
        if(!StringUtils.isEmpty(criteria.getSndj())) {
            conditionSql1 += " and n.bis_zhsq_sndj = :sndj ";
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqDyjsrq2())) {
            conditionSql1 += " and n.bis_zhsq_dyjsrq <= :jsrq2 ";
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqDyjsrq1())) {
            conditionSql1 += " and n.bis_zhsq_dyjsrq >= :jsrq1 ";
        }

        sql += conditionSql1 + " order by n.lrsj desc ";

        Query query = em.createNativeQuery(sql);

        if(!StringUtils.isEmpty(criteria.getUserId())) {
            query.setParameter("ryglId", criteria.getUserId());
        }
        if(!StringUtils.isEmpty(criteria.getDdbm())) {
            query.setParameter("ddbm", criteria.getDdbm());
        }
        if(!StringUtils.isEmpty(criteria.getZt())) {
            query.setParameter("zt", criteria.getZt());
        }
        if(!StringUtils.isEmpty(criteria.getTcq())) {
            query.setParameter("tcq", criteria.getTcq());
        }
        if(!StringUtils.isEmpty(criteria.getZhlb())) {
            query.setParameter("zhlb", criteria.getZhlb());
        }
        if(!StringUtils.isEmpty(criteria.getXm())) {
            query.setParameter("xm", "%" + criteria.getXm() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            query.setParameter("sfz", "%" + criteria.getSfz() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSbgsId())) {
            query.setParameter("sbgsId", criteria.getSbgsId());
        }
        if(!StringUtils.isEmpty(criteria.getInZt())) {
            query.setParameter("ztList", criteria.getZtList());
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqCblx())) {
            query.setParameter("cblx", criteria.getBisZhsqCblx());
        }
        if(!StringUtils.isEmpty(criteria.getSndj())) {
            query.setParameter("sndj", criteria.getSndj());
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqDyjsrq2())) {
            query.setParameter("jsrq2", criteria.getBisZhsqDyjsrq2());
        }
        if(!StringUtils.isEmpty(criteria.getBisZhsqDyjsrq1())) {
            query.setParameter("jsrq1", criteria.getBisZhsqDyjsrq1());
        }
        List result = query.getResultList();
        List<Map<String, Object>> list = new ArrayList<>();
        int cnt = 0;
        for (Object obj : result) {
            cnt++;
            Object[] arr = (Object[]) obj;
            Map<String,Object> map = new LinkedHashMap<>();
            map.put("序号", cnt);
            map.put("申请状态", arr[48]);
            map.put("申请时间", arr[44]);
            map.put("医保卡号", arr[27]);
            map.put("姓名", arr[3]);
            map.put("性别", arr[4]);
            map.put("身份证", arr[5]);
            map.put("参保类型", arr[47]);
            map.put("年龄", arr[16]);
            map.put("待遇开始日期", arr[8]);
            map.put("待遇结束日期", arr[9]);
            map.put("失能等级", arr[14]);
            map.put("ADL评分", arr[11]);
            map.put("统筹区", arr[7]);
            map.put("定点编码", arr[35]);
            map.put("护理机构", arr[37]);
            map.put("评估人员", arr[39]);
            map.put("评估时间", arr[42]);
            map.put("复核人员", arr[41]);
            map.put("复核时间", arr[43]);
            list.add(map);


        }

        FileUtil.downloadExcel(list, response);
    }

    @Override
    public Object queryFwmxcs(GeneralQueryCriteria criteria) {


        String conditionSql1 = "";

        if(!StringUtils.isEmpty(criteria.getDdbm())) {
            conditionSql1 += " and r4.Bis_Gsgl_Yljgdm=:ddbm ";
        }
        if(!StringUtils.isEmpty(criteria.getRyrq1())) {
            conditionSql1 += " and r2.bis_jzdj_ryrq >= :ryrq1 ";
        }
        if(!StringUtils.isEmpty(criteria.getRyrq2())) {
            conditionSql1 += " and r2.bis_jzdj_ryrq <= :ryrq2 ";
        }
        String tempSql =  "select   homecatogy, aka091 ,nvl( cnt, 0)    from ( " +
                "select R1.AKC222, R1.AKC223 , COUNT(*)   cnt    " +
                "                from kc22 r1, bis_jzdj r2, bis_zhsq r3, bis_gsgl r4 " +
                "                where r1.akc190 = r2.bis_jzdj_lsh " +
                "                and r2.bis_jzdj_zhsq_id = r3.bis_zhsq_id " +
                "                and r3.bis_zhsq_hljg_id = r4.bis_gsgl_id " +
                "                and r2.bis_jzdj_cyzt ='1' " +
                "                and (r1.akc222 like 'JJZHSH%' OR  r1.akc222 like 'JJZHYL%')  " +
                "                and (r1.akc222 !='JJZHSHHL000' AND  r1.akc222 !='JJZHYLHL000')  " +
                "                AND (r1.AKB020!='6001' AND r1.AKB020!='6002' ) " +
                "                GROUP BY R1.AKC222, R1.AKC223 " +
                "                ) t1 right join " +
                "(" +
                "select case when INSTR(t.aka090, 'JJZHSH') !=0 then '生活' else '医疗' end homecatogy, t.aka090, t.aka091 from KA03 t where (t.aka090 like 'JJZHSH%' or t.aka090 like 'JJZHYL%' ) " +
                "and    (t.aka090 !='JJZHSHHL000' AND  t.aka090 !='JJZHYLHL000')  " +
                "and t.aka065 = '1' " +
                ")  t2   on  t1.AKC222 = t2.aka090 " +
                "order by nvl( cnt, 0) desc" +
                "                 ";



        Query query = em.createNativeQuery(tempSql);

        if(!StringUtils.isEmpty(criteria.getDdbm())) {
            query.setParameter("ddbm", criteria.getDdbm());
        }
        if(!StringUtils.isEmpty(criteria.getRyrq1())) {
            query.setParameter("ryrq1", criteria.getRyrq1());
        }
        if(!StringUtils.isEmpty(criteria.getRyrq2())) {
            query.setParameter("ryrq2", criteria.getRyrq2());
        }

        List result = query.getResultList();
        List<GeneralQueryZhgVo> hljgDspVos = new ArrayList<>();
        for (Object obj : result) {
            Object[] arr = (Object[]) obj;
            GeneralQueryZhgVo vo = new GeneralQueryZhgVo();
            vo.setAkc222(arr[0]);
            vo.setAkc223(arr[1]);
            vo.setFwmxcs(Long.parseLong(arr[2].toString()));
            hljgDspVos.add(vo);
        }

        Map<String,Object> m = PageUtil.toPage(hljgDspVos, hljgDspVos.size());
        return m;
    }
}
